package hello;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.RandomStringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

/**
 * 
 * @author Rahul_Mittal
 *
 */
public class MobileVistorDB {
	
	SimpleDriverDataSource dataSource ;
	JdbcTemplate jdbcTemplate ;
	String urlString = "jdbc:sqlserver://54.179.156.168;databaseName=TBOOK";
	String table_name = "[dbo].[VISITOR_VERIF_MOBILE]";
	String select_query = "SELECT *  FROM " + table_name;

	public MobileVistorDB() {
		super();
		dataSource = new SimpleDriverDataSource();
		dataSource.setDriverClass(com.microsoft.sqlserver.jdbc.SQLServerDriver.class);
		dataSource.setUsername("sa");
		dataSource.setUrl(urlString);
		dataSource.setPassword("S!va@123");
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public void createtVisitor(MobileVisitor visitor){
		System.out.println("createtVisitor() called. visitor :" + visitor);
		jdbcTemplate.update("INSERT INTO "+ table_name +" ([APPLICANT_NAME],[SPOUSE_NAME],"
						+ " [PRESENT_ADDRESS], [PASSPORT_NUM], [VOTER_ID], [RATION_CARD_NUM],"
						+ "[PROFILE_IMAGE], [PROFILE_IMAGE1]) values(?,?,?,?, ?,?,?,?)",
						visitor.getApplicant_name(), visitor.getSpouse_name(), visitor.getPresent_address(),
						visitor.getPassport_num(), visitor.getVoter_id(), visitor.getRation_card_num(), visitor.getProfile_image(), visitor.getProfile_image2());
	}
	
	public List<MobileVisitor> readAllVisitors(){
		System.out.println("selectAllVisitors() called.");
        List<MobileVisitor> results = jdbcTemplate.query(select_query, new Object[] {},
                new RowMapper<MobileVisitor>() {
                    @Override
                    public MobileVisitor mapRow(ResultSet rs, int rowNum) throws SQLException {
                        return new MobileVisitor(rs.getLong("ID"), rs.getString("APPLICANT_NAME"),
                                rs.getString("SPOUSE_NAME"), rs.getString("PRESENT_ADDRESS"), rs.getString("PASSPORT_NUM"),
                                rs.getString("VOTER_ID"), rs.getString("RATION_CARD_NUM"), rs.getString("PROFILE_IMAGE"), 
                                rs.getString("PROFILE_IMAGE1"));
                    }
                });
        for (MobileVisitor visitor : results) {
            System.out.println(visitor);
        }
		return results;
	}
	
	public void updateVisitor(MobileVisitor visitor){
		
	}
	
	public void deleteVisitorByName(String name){
		
	}
	
	public static void main(String[] args) {
		MobileVistorDB dbaccess = new MobileVistorDB();
		System.out.println("### Creating Visitor.");
		MobileVisitor visitor = new MobileVisitor(0L, "ApplicantName_"+RandomStringUtils.randomAlphanumeric(20) ,
						"SpounseName_"+RandomStringUtils.randomAlphanumeric(15), "Address_"+RandomStringUtils.randomAlphanumeric(20),
						"PassportNo_"+RandomStringUtils.randomAlphanumeric(10), "VoterID_"+RandomStringUtils.randomAlphanumeric(8),
						"RationCardNo_"+RandomStringUtils.randomAlphanumeric(7), "ProfileImage_"+RandomStringUtils.randomAlphanumeric(50),
						"ProfileImage2_"+RandomStringUtils.randomAlphanumeric(50));
		dbaccess.createtVisitor(visitor);
        System.out.println("### Querying all Visitors.");
		dbaccess.readAllVisitors();
	}

}
